﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using StudentManagementSystem.Models;

namespace StudentManagementSystem.Repositories
{
    public class ClassRepository : BaseRepository
    {
        public ClassRepository(DB.DBContext dbContext) : base(dbContext) { }

        public int Create(Class @class)
        {
            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        using (var command = new SQLiteCommand(connection))
                        {
                            command.CommandText = @"
                                INSERT INTO Classes (Name, SchoolId, CreatedAt)
                                VALUES (@Name, @SchoolId, @CreatedAt);
                                SELECT last_insert_rowid();";
                            command.Parameters.AddWithValue("@Name", @class.Name);
                            command.Parameters.AddWithValue("@SchoolId", @class.SchoolId);
                            command.Parameters.AddWithValue("@CreatedAt", @class.CreatedAt);
                            @class.Id = Convert.ToInt32(command.ExecuteScalar());
                        }

                        transaction.Commit();
                        LogAction("CREATE", "Classes", @class.Id.ToString(), $"Name: {@class.Name}, SchoolId: {@class.SchoolId}");
                        return @class.Id;
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public Class GetById(int id)
        {
            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
                        SELECT c.*, s.Name AS SchoolName
                        FROM Classes c
                        JOIN Schools s ON c.SchoolId = s.Id
                        WHERE c.Id = @Id";
                    command.Parameters.AddWithValue("@Id", id);

                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return new Class
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("Id")),
                                Name = reader.GetString(reader.GetOrdinal("Name")),
                                SchoolId = reader.GetInt32(reader.GetOrdinal("SchoolId")),
                                CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt")),
                                School = new School
                                {
                                    Id = reader.GetInt32(reader.GetOrdinal("SchoolId")),
                                    Name = reader.GetString(reader.GetOrdinal("SchoolName"))
                                }
                            };
                        }
                    }
                }
            }

            return null;
        }

        public List<Class> GetAll()
        {
            var classes = new List<Class>();

            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
                        SELECT c.*, s.Name AS SchoolName
                        FROM Classes c
                        JOIN Schools s ON c.SchoolId = s.Id
                        ORDER BY s.Name, c.Name";

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            classes.Add(new Class
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("Id")),
                                Name = reader.GetString(reader.GetOrdinal("Name")),
                                SchoolId = reader.GetInt32(reader.GetOrdinal("SchoolId")),
                                CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt")),
                                School = new School
                                {
                                    Id = reader.GetInt32(reader.GetOrdinal("SchoolId")),
                                    Name = reader.GetString(reader.GetOrdinal("SchoolName"))
                                }
                            });
                        }
                    }
                }
            }

            return classes;
        }

        public List<Class> GetBySchoolId(int schoolId)
        {
            var classes = new List<Class>();

            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
                        SELECT c.*, s.Name AS SchoolName
                        FROM Classes c
                        JOIN Schools s ON c.SchoolId = s.Id
                        WHERE c.SchoolId = @SchoolId
                        ORDER BY c.Name";
                    command.Parameters.AddWithValue("@SchoolId", schoolId);

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            classes.Add(new Class
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("Id")),
                                Name = reader.GetString(reader.GetOrdinal("Name")),
                                SchoolId = reader.GetInt32(reader.GetOrdinal("SchoolId")),
                                CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt")),
                                School = new School
                                {
                                    Id = reader.GetInt32(reader.GetOrdinal("SchoolId")),
                                    Name = reader.GetString(reader.GetOrdinal("SchoolName"))
                                }
                            });
                        }
                    }
                }
            }

            return classes;
        }

        public void Update(Class @class)
        {
            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        using (var command = new SQLiteCommand(connection))
                        {
                            command.CommandText = @"
                                UPDATE Classes
                                SET Name = @Name,
                                    SchoolId = @SchoolId
                                WHERE Id = @Id";
                            command.Parameters.AddWithValue("@Name", @class.Name);
                            command.Parameters.AddWithValue("@SchoolId", @class.SchoolId);
                            command.Parameters.AddWithValue("@Id", @class.Id);
                            command.ExecuteNonQuery();
                        }

                        transaction.Commit();
                        LogAction("UPDATE", "Classes", @class.Id.ToString(), $"Name: {@class.Name}, SchoolId: {@class.SchoolId}");
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public void Delete(int id)
        {
            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // 先删除关联的学生
                        using (var studentCommand = new SQLiteCommand(connection))
                        {
                            studentCommand.CommandText = "DELETE FROM Students WHERE ClassId = @ClassId";
                            studentCommand.Parameters.AddWithValue("@ClassId", id);
                            studentCommand.ExecuteNonQuery();
                        }

                        // 再删除班级
                        using (var classCommand = new SQLiteCommand(connection))
                        {
                            classCommand.CommandText = "DELETE FROM Classes WHERE Id = @Id";
                            classCommand.Parameters.AddWithValue("@Id", id);
                            classCommand.ExecuteNonQuery();
                        }

                        transaction.Commit();
                        LogAction("DELETE", "Classes", id.ToString());
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}